package csv;

import swimming.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Dao {

    public List<Object[]> selectAll() throws SQLException{
        List<Object[]> ls = new ArrayList<Object[]>();
        int money1 = 0;
        int money2 = 0;
        Connection con = DBUtils.getConnection();
        PreparedStatement st = con.prepareStatement("select  * from 消费情况");
        ResultSet re = st.executeQuery();
        ls = result(con,st,re);
        st = con.prepareStatement("select  count(*) from(select count(电话) from 消费情况 group by 电话 ) a");
        re = st.executeQuery();
        while (re.next()) {
            Object[] r = new Object[5];
            r[0] = "";
            r[1] = "";
            r[2] = "";
            r[3] = "会员数：";
            r[4] = re.getString(1);
            //q[0] = re.getString(1);

            ls.add(r);
        }
        st = con.prepareStatement("select sum(存款金额) from 消费情况,会员 where 消费情况.电话=会员.电话 and 会员.类型='储值卡'");
        re = st.executeQuery();
        while (re.next()) {
            Object[] r = new Object[5];
            r[0] = "";
            r[1] = "";
            r[2] = "";
            r[3] = "储值卡会员预存总金额：";
            r[4] = re.getString(1);
            ls.add(r);
        }
        st = con.prepareStatement("select sum(消费金额) from 消费情况,会员 where 消费情况.电话=会员.电话 and 会员.类型='折扣卡'");
        re = st.executeQuery();
        while (re.next()) {
            Object[] r = new Object[5];
            r[0] = "";
            r[1] = "";
            r[2] = "";
            r[3] = "折扣卡会员消费总金额：";
            r[4] = re.getString(1);
            ls.add(r);
        }
        st = con.prepareStatement("SELECT sum(人数) from 消费");
        re = st.executeQuery();
        while (re.next()) {
            Object[] r = new Object[5];
            r[0] = "";
            r[1] = "";
            r[2] = "";
            r[3] = "客流量为：";
            r[4] = re.getString(1);
            ls.add(r);
        }
        st = con.prepareStatement("SELECT sum(aa1)+sum(bb1) FROM (select sum(存款金额) as aa1 from 消费情况,会员 where 消费情况.电话=会员.电话 and 会员.类型='储值卡')a,(select sum(消费金额) as bb1 from 消费情况,会员 where 消费情况.电话=会员.电话 and 会员.类型='折扣卡')b;");
        re = st.executeQuery();
        while (re.next()) {
            Object[] r = new Object[5];
            r[0] = "";
            r[1] = "";
            r[2] = "";
            r[3] = "总收入：";
            r[4] = re.getString(1);
            ls.add(r);
        }
        DBUtils.close(con,st,re);
        return ls;
    }
    public List<Object[]> result(Connection con, PreparedStatement st, ResultSet re) throws SQLException {
        List<Object[]> list = new ArrayList<>();
        try {
            while (re.next()) {
                Object[] r = new Object[5];
                r[0] = re.getString("存款金额");
                r[1] = re.getString("结存金额");
                r[2] = re.getString("时间");
                r[3] = re.getString("电话");
                r[4] = re.getString("消费金额");
                list.add(r);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}